<?xml version="1.0" encoding="windows-1250"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:output method="xml" encoding="windows-1250" /> 
 
  <xsl:param name="dbtype">msaccess</xsl:param>
  
  <xsl:variable name="dbmappingfile">dbmaps/<xsl:value-of select="$dbtype"/>.xml</xsl:variable>
  
  <xsl:variable name = "abc">abcdefghijklmnopqrstuvwxyz</xsl:variable>
  <xsl:variable name = "ABC">ABCDEFGHIJKLMNOPQRSTUVWXYZ</xsl:variable>

  <xsl:template match="/">
    <sql>
      <xsl:apply-templates select="Concepts/Concept[count(CProperties/CProperty)&gt;0]"/>
    </sql>
  </xsl:template>

  <xsl:template match="Concept">

      <xsl:variable name="tnametmp">
        <xsl:choose>
          <xsl:when test="@pname"><xsl:value-of select="@pname"/></xsl:when>
          <xsl:otherwise><xsl:value-of select="@name"/></xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
      <xsl:variable name="tname">
        <xsl:call-template name="getNonReserved">
          <xsl:with-param name="name" select="$tnametmp"/>
        </xsl:call-template>
      </xsl:variable>

      <xsl:text/>CREATE TABLE <xsl:value-of select="$tname"/> (&#13;&#10;<xsl:text/>
      <xsl:for-each select="CProperties/CProperty">
        <xsl:variable name="cpname">
          <xsl:call-template name="getNonReserved">
            <xsl:with-param name="name" select="@name"/>
          </xsl:call-template>
        </xsl:variable>
        <xsl:text>  </xsl:text><xsl:value-of select="$cpname"/><xsl:text> </xsl:text>
        <xsl:variable name="cptypetmp">
          <xsl:choose>
            <xsl:when test="@type='url'">varchar</xsl:when>
            <xsl:when test="@type='xhtml'">varchar</xsl:when>
            <xsl:when test="@type='longvarchar'">varchar</xsl:when>
            <xsl:when test="@type='password'">varchar</xsl:when>
            <xsl:when test="@type='file'">varchar</xsl:when>
            <xsl:when test="@type='boolean'">int</xsl:when>
            <xsl:when test="@type='boolean_txt'">char</xsl:when>
            <xsl:when test="@type='date'">timestamp</xsl:when>
            <xsl:otherwise><xsl:value-of select="@type"/></xsl:otherwise>
          </xsl:choose>
        </xsl:variable>
        <xsl:variable name="cptype">
          <xsl:choose>
            <xsl:when test="$cptypetmp='varchar' and number(@len)&gt;255 and $dbtype='msaccess'">memo</xsl:when>
            <xsl:when test="$cptypetmp='varchar' and number(@len)&gt;255 and $dbtype='mysql'">longtext</xsl:when>
            <xsl:when test="@key='1' or string-length(@ref)&gt;1">char</xsl:when>
            <xsl:otherwise><xsl:value-of select="$cptypetmp"/></xsl:otherwise>
          </xsl:choose>
        </xsl:variable>
        <xsl:variable name="cplen">
          <xsl:choose>
            <xsl:when test="@type='boolean_txt'">5</xsl:when>
            <xsl:when test="@key='1' or string-length(@ref)&gt;1">36</xsl:when>
            <xsl:otherwise><xsl:value-of select="@len"/></xsl:otherwise>
          </xsl:choose>
        </xsl:variable>    

        <xsl:value-of select="$cptype"/><xsl:text/>
        <xsl:if test="$cptype='varchar' or $cptype='char'">(<xsl:value-of select="$cplen"/>)</xsl:if>
        <xsl:if test="@mandat='1'"> NOT NULL</xsl:if>
        <xsl:if test="position()!=last()">, &#13;&#10;</xsl:if>
      </xsl:for-each>
      <xsl:if test="count(CProperties/CProperty[@key='1'])&gt;0">
        <xsl:text>,&#13;&#10;  </xsl:text>CONSTRAINT PrimaryKey PRIMARY KEY (<xsl:text/>
          <xsl:for-each select="CProperties/CProperty[@key='1']">
            <xsl:variable name="cpname">
              <xsl:call-template name="getNonReserved">
                <xsl:with-param name="name" select="@name"/>
              </xsl:call-template>
            </xsl:variable>
            <xsl:value-of select="$cpname"/>
            <xsl:if test="position()!=last()">,</xsl:if>
          </xsl:for-each>
        <xsl:text>)</xsl:text>
      </xsl:if>
      <xsl:text/>&#13;&#10;); &#13;&#10;&#13;&#10;<xsl:text/>

  </xsl:template>

  <xsl:template name="getNonReserved">    
    <xsl:param name="name"/>
    <xsl:variable name="uname"><xsl:value-of select="translate($name,$abc,$ABC)"/></xsl:variable>
    <xsl:variable name="reserved" select="document(translate($dbmappingfile, '\', '/'))/DBMapping/ReservedWords/Reserved[translate(@word,$abc,$ABC)=$uname]"/>
    <xsl:choose>
      <xsl:when test="$reserved">
        <xsl:value-of select="concat('xxx',$name)"/>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="$name"/>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>


</xsl:stylesheet>
       
 
